﻿/*******************************************************************************
 * Copyright © 2017 FriendStar.Framework 版权所有
 * Author: FZH
 * Description: FriendStar快速开发平台
 * Website：http://www.FriendStar.cn
*********************************************************************************/
using System.Collections.Generic;
using FriendStar.Code;
using System.Data.SqlClient;
using FriendStar.Application.VBaseData;
using System.Text;
using System.Data;
using System;

namespace FriendStar.Application.BaseData
{
    /// <summary>
	/// 模具档案对应的数据应用功能类。
	/// </summary>
	public class MouldArchiveApp
    {


        public object GetMouldArchivies(Pagination pagination, string moldNO, string moldName, string assetsNO, string moldUnit, string moldWorkshop, string workpieceNo, string manuUnit, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" SELECT 
ISNULL(MaintainStatus, 'c') + ',' + Moldid mstatusid,
                        case MaintainStatus when 0 then '编辑'  when 2 then '冻结' when 1 then '发布' else '未知' end MaintainStatusName,
                        MaintainStatus,
	                        Moldid,
	                        RegistrationDate,
	                        WorkpieceNo,
	                        WorkpieceName,	
                        ProcessNO	,
                        MoldNO	,
                        MoldName	,
                        AssetsNO	,
                        MoldNum,	
                        MoldLife	,
                        MoldSize	,
                        CloseHeight	,
                        MoldWeight	,
                        TopWeight	,
                        CopyMold	,
                        MoldProductionNO	,
                        MoldCategory	,
                        MoldCategoryName	,
                        MoldType	,
                        MoldTypeName	,
                        TransDate	,
                        TransValues	,
                        RightUnit	,
                        RightUnitName	,
                        PutDate	,
                        UsedPress	,
                        IsDisable	,
                        ManuUnit	,
                        ManuUnitName	,
                        MoldUnit	,
                        MoldUnitName	,
                        MoldWorkshop	,
                        MoldWorkshopName	,
                        VehicleDistr	,
                        MaintenCycle	,
                        RinseCycle	,
                        ProjectUnit	,
                        ProjectUnitName	,
                        ProjectNo	,
                        MoldExplain	,
                        InputUser	,
                        InputDate,
	                        InputTime	,
                        FAuditingBy	,
                        FAuditingDate	,
                        FAuditingTime
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (

				                        ORDER BY {4}
			                        ) num,
			                        *
		                        FROM
			                        {3}
	                        )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_Mol_11_DA_Mold";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(moldNO))
            {
                conn.Append(string.Format(" and moldNO = '{0}'", moldNO));
            }

            if (!string.IsNullOrEmpty(moldName))
            {
                conn.Append(string.Format(" and moldName = '{0}'", moldName));
            }

            if (!string.IsNullOrEmpty(assetsNO))
            {
                conn.Append(string.Format(" and assetsNO = '{0}'", moldNO));
            }

            if (!string.IsNullOrEmpty(moldUnit))
            {
                conn.Append(string.Format(" and moldUnit = '{0}'", moldUnit));
            }

            if (!string.IsNullOrEmpty(moldWorkshop))
            {
                conn.Append(string.Format(" and moldWorkshop = '{0}'", moldWorkshop));
            }

            if (!string.IsNullOrEmpty(workpieceNo))
            {
                conn.Append(string.Format(" and workpieceNo = '{0}'", workpieceNo));
            }

            if (!string.IsNullOrEmpty(manuUnit))
            {
                conn.Append(string.Format(" and manuUnit = '{0}'", manuUnit));
            }


            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx,pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }



        public object ChangeMouldArchive(string type, string ids, string curUser, DateTime now)
        {
            int result = 0;

            List<string> list = null;

            string sql = string.Empty;

            string[] strs = null;

            sql = @"update Mol_11_DA_Mold set 
                        MaintainStatus = @MaintainStatus,
                        FAuditingBy = @FAuditingBy,
                        FAuditingDate = @FAuditingDate,
                        FAuditingTime = @FAuditingTime
            where Moldid in ({0})";

            strs = ids.Split(','); 

            list = new List<string>();

            foreach (string item in strs)
            {
                list.Add(string.Format("'{0}'", item));
            }

            sql = string.Format(sql, string.Join(",", list.ToArray()));

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@MaintainStatus", type),
                new SqlParameter("@FAuditingBy", curUser),
                new SqlParameter("@FAuditingDate", now.ToString("yyyy-MM-dd")),
                new SqlParameter("@FAuditingTime", now.ToString("HH:mm:ss"))
            };

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object GetMouldArchiviePartNumbers(Pagination pagination, string fProductionNO, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT 
                        FProductionNO,FProductionName
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (

				                        ORDER BY FProductionNO
			                        ) num,
			                        *
		                        FROM
			                        {3}
	                        )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "ZSJ_11_LJ_Production";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO like '%{0}%'", fProductionNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName);

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object InsertMouldArchive(Dictionary<string, object> item)
        {
            int result = 0;

            string sql = @"insert into  Mol_11_DA_Mold ({0}) values({1})";

            List<string> conn = null, conn2 = null,columns = null;

            SqlParameter[] pars = null;

            pars = new SqlParameter[item.Count];

            conn = new List<string>();

            conn2 = new List<string>();

            columns = new List<string>();

            foreach (string key in item.Keys)
            {
                conn.Add(string.Format("{0}", key));

                conn2.Add(string.Format("@{0}", key));

                columns.Add(key);
            }

            sql = string.Format(sql, string.Join(",", conn.ToArray()), string.Join(",", conn2.ToArray()));

            for (int i = 0; i < item.Keys.Count; i++)
            {
                pars[i] = new SqlParameter(string.Format("@{0}", columns[i]), item[columns[i]]);
            }

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object UpdateMouldArchive(Dictionary<string, object> item)
        {
            int result = 0, count = item.Count;

            string sql = @"update Mol_11_DA_Mold set 
            {0}
            where Moldid =@Moldid";

            List<string> conn = null,columns = null;

            SqlParameter[] pars = null;

            string moldid = item["Moldid"].ToString();

            DateTime now = DateTime.Now;

            item.Remove("Moldid");

            pars = new SqlParameter[count];

            conn = new List<string>();

            columns = new List<string>();

            foreach (string key in item.Keys)
            {
                conn.Add(string.Format("{0} = @{0}\r\n", key));

                columns.Add(key);
            }

            sql = string.Format(sql, string.Join(",", conn.ToArray()));

            for (int i = 0; i < item.Keys.Count; i++)
            {
                pars[i] = new SqlParameter(string.Format("@{0}", columns[i]), item[columns[i]]);
            }

            pars[item.Count] = new SqlParameter("@Moldid", moldid);

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object GetUseUnits(string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            Dictionary<string, object> item = null;

            string sql = @" select MakeLineNO,MakeLineShortName,MakeLineNO + ' ' +MakeLineShortName  MakeLineNOMakeLineShortName  from LXK_16_LX_MakeLine where UseUnit =@UseUnit  order by MakeLineNO";

            result = new List<Dictionary<string, object>>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit", useUnit)
            };

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetModCategories(string useUnit,string FClassNO)
        {
            List<Dictionary<string, object>> result = null;

            Dictionary<string, object> item = null;

            string sql = @" select FTypeNO,FTypeName,FTypeNOName  from dbo.V_BUS_SJ_Dictionary  WHERE UseUnit =@UseUnit AND FClassNO=@FClassNO  ORDER BY  FTypeNO";

            result = new List<Dictionary<string, object>>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit", useUnit),
                new SqlParameter("@FClassNO", FClassNO)
            };

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetMouldArchivieUnits(Pagination pagination, string FProductNO, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT 
                        FProductNO, FProductName
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (

				                        ORDER BY FProductNO
			                        ) num,
			                        *
		                        FROM
			                        {3}
	                        )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "LXK_11_ML_ProductList";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(FProductNO))
            {
                conn.Append(string.Format(" and FProductNO like '%{0}%'", FProductNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName);

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetMouldArchivie(string id)
        {
            Dictionary<string, object> result = null;

            string sql = @" SELECT 
ISNULL(MaintainStatus, 'c') + ',' + Moldid mstatusid,
                        case MaintainStatus when 0 then '编辑'  when 2 then '冻结' when 1 then '发布' else '未知' end MaintainStatusName,
                        MaintainStatus,
	                        Moldid,
	                        RegistrationDate,
	                        WorkpieceNo,
	                        WorkpieceName,	
                        ProcessNO	,
                        MoldNO	,
                        MoldName	,
                        AssetsNO	,
                        MoldNum,	
                        MoldLife	,
                        MoldSize	,
                        CloseHeight	,
                        MoldWeight	,
                        TopWeight	,
                        CopyMold	,
                        MoldProductionNO	,
                        MoldCategory	,
                        MoldCategoryName	,
                        MoldType	,
                        MoldTypeName	,
                        TransDate	,
                        TransValues	,
                        RightUnit	,
                        RightUnitName	,
                        PutDate	,
                        UsedPress	,
                        IsDisable	,
                        ManuUnit	,
                        ManuUnitName	,
                        MoldUnit	,
                        MoldUnitName	,
                        MoldWorkshop	,
                        MoldWorkshopName	,
                        VehicleDistr	,
                        MaintenCycle	,
                        RinseCycle	,
                        ProjectUnit	,
                        ProjectUnitName	,
                        ProjectNo	,
                        MoldExplain	,
                        FUpdateBy	,
                        FUpdateDate,
	                        FUpdateTime	,
                        FAuditingBy	,
                        InputDate	,
                        InputTime,
                        InputUser
                        FROM
	                        V_Mol_11_DA_Mold
                where  Moldid =@Moldid";

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@Moldid", id)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                if (reader.Read())
                {
                    result = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        result.Add(reader.GetName(i), reader[i]);
                    }
                }
            }

            return result;
        }

        public object DeleteMouldArchive(string id)
        {
            int result = 0;

            string sql = @"delete FROM Mol_11_DA_Mold
            where Moldid =@Moldid";

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@Moldid", id)
            };

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }
    }
}